// $codepro.audit.disable booleanMethodNamingConvention, nullPointerDereference, com.instantiations.assist.eclipse.analysis.unusedReturnValue

/**
 * Database.java
 */
package edu.gatech.coc.betameisters.utilities;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;

import edu.gatech.coc.betameisters.infoholders.Appointment;
import edu.gatech.coc.betameisters.infoholders.DoctorsOrders;
import edu.gatech.coc.betameisters.infoholders.MedicalHistory;
import edu.gatech.coc.betameisters.people.Doctor;
import edu.gatech.coc.betameisters.people.Nurse;
import edu.gatech.coc.betameisters.people.Patient;
import edu.gatech.coc.betameisters.people.Person;
import edu.gatech.coc.betameisters.people.SystemAdmin;


/**
 * Database backend for our system
 * @author William
 * @version 1.0
 */
public class Database {
	/**
	 * Database login name
	 */
	private static final String DATABASE_LOGIN = "root";
	
	/**
	 * Database password
	 */
	private static final String DATABSE_PASSWORD = "root";
	
	/**
	 * URL for the database
	 */
	private static final String DATABSE_URL = "jdbc:mysql://localhost/doctorsoffice";
	
	/**
	 * Instance of a connection
	 */
	private static Connection conn; // $codepro.audit.disable
	
	/**
	 * Array of tables that refer to people
	 */
	private static final String[] PEOPLE_TABLES = 
		{"patient", "nurse", "doctor", "systemadmin"};  // $codepro.audit.disable modifierOrder, constantNamingConvention
	
	/**
	 * Default constructor for the database
	 */
	public Database() {
		connect(); // $codepro.audit.disable com.instantiations.assist.eclipse.analysis.unusedReturnValue
	}
	
	/**
	 * Database connect
	 * @return something
	 */
	public static Connection connect() {
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance(); // $codepro.audit.disable
			conn = DriverManager.getConnection(DATABSE_URL, 
					DATABASE_LOGIN, DATABSE_PASSWORD);
		}
		catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
		}
		catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
		}
		catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
		}
		catch (SQLException ex)           {
			System.err.println(ex.getMessage());
		}
		
		return conn;
	}

	/**
	 * Gets the connection status
	 * @return conn
	 */
	public static Connection getConnection() {
		return conn;
	}
	
	/**
	 * Close connection
	 */
	public static void closeConnection() {
		try {
			conn.close(); // $codepro.audit.disable closeInFinally
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Checks to see if a username is taken on the database
	 * 
	 * @param username Username to check
	 * @return true if the username is unique, false otherwise
	 */
	public static boolean userNameAvailable(String username) { // $codepro.audit.disable booleanMethodNamingConvention
		// connect to the database
		connect();
		
		// generate a query that includes all user tables
		String query = "";
		final String delimiter = " UNION ";
		
		for(String str : PEOPLE_TABLES) {
			query += "SELECT `username` FROM `" + str +  // $codepro.audit.disable stringConcatenationInLoop
			"` WHERE username = '" + username + "'" + delimiter;
		}
		// delete the last delimiter
		query = query.substring(0, query.length() - delimiter.length());
		
		// try to execute the query
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			
			// if the result set is populated at all,
			// the username is taken
			if(rs.next()) { // $codepro.audit.disable
				return false;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			closeConnection();
		}
		return true;
	}
	
	/**
	 * Loads a patient object from the database
	 * to an object.
	 * @param username id of the patient
	 * @return the instance of the patient
	 */
	public static Patient loadPatient(String username) {
		connect();
		
		Patient pat = null;

		final String query = "SELECT patient.*, doctors_orders.orders " +
				"FROM patient " +
				"LEFT JOIN doctors_orders " +
				"ON patient.username = doctors_orders.patient_username " +
				"WHERE patient.username ='" + username + "'";
		
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			while (rs.next()){		 // $codepro.audit.disable
		
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");
				
				// Deserialize the doctors orders and medical history
				DoctorsOrders orders = (DoctorsOrders) Serializer.fromString(
						rs.getString("orders"));
				MedicalHistory medHist = (MedicalHistory) Serializer.fromString(
						rs.getString("medical_history"));
				
				// load it into the object
				pat = new Patient(username, encryptPass, firstName, 
						lastName, medHist, orders);
			}
		} catch (SQLException ex){
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		// return it
		return pat;
	}
	
	/**
	 * Loads a Nurse object from the database based on
	 * its corresponding id on the database
	 * @param username
	 * @return the nurse instance
	 */
	public static Nurse loadNurse(String username) {
		connect();
		Nurse nur = null;
		
		final String query = "SELECT first_name, last_name, `password`, patient" +
				" FROM nurse where username ='" + username + "'";
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			while (rs.next()){ // $codepro.audit.disable
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");
				String currentPatient = rs.getString("patient");
				
				nur = new Nurse(username, encryptPass, firstName, lastName, 
							loadPatient(currentPatient));
			}
		} catch (SQLException ex){
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		// return it
		return nur;
	}
	
	/**
	 * Loads a Nurse object from the database based on
	 * its corresponding id on the database
	 * @param username id number
	 * @return the nurse instance
	 */
	public static Doctor loadDoctor(String username) {
		connect();
		Doctor d = null;
		
		final String query = "SELECT first_name, last_name, `password`, patient" +
				" FROM doctor where username ='" + username + "'";
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			while (rs.next()){ // $codepro.audit.disable
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");
				String currentPatient = rs.getString("patient");
				
				d = new Doctor(username, encryptPass, firstName, lastName, 
						loadPatient(currentPatient));
			}
		} catch (SQLException ex){
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		// return it
		return d;
	}
	
	/**
	 * Loads a system admin from the database
	 * @param username Username of admin
 	 * @return the admin object from the database
	 */
	public static SystemAdmin loadSystemAdmin(String username) {
		connect();
		SystemAdmin adm = null;
		
		final String query = "SELECT first_name, last_name, `password`" +
				" FROM systemadmin where username ='" + username + "'";
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			while (rs.next()){ // $codepro.audit.disable
				// get the info from the database
				String firstName = rs.getString("first_name");
				String lastName = rs.getString("last_name");
				String encryptPass = rs.getString("password");
				
				adm = new SystemAdmin(username, encryptPass, firstName, lastName);
			}
		} catch (SQLException ex){
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		// return it
		return adm;
	}
	
	/**
	 * Goes through all user tables and deletes a user
	 * by their username
	 * 
	 * @param username the username of the user to delete
	 */
	public static void deleteByUsername(String username) {
		connect();
		try{
			// Create a query for deletion
			final Statement st = conn.createStatement();
			
			// Execute the queries
			for(String str : PEOPLE_TABLES) {
				st.executeUpdate("DELETE FROM " + str + 
						" WHERE username='" + username + "'");
			}
		}
		catch (SQLException ex){
			System.err.println(ex.getMessage());
		} finally {
			// close the connection
			closeConnection();
		}
	}
	
	/**
	 * Executes the specified update query
	 * @param query The query to be executed
	 * @return true if successful, false if there were errors
	 */
	public static boolean executeUpdateQuery(String query) { // $codepro.audit.disable
		final Connection conn = connect();
		
		try {
			final Statement st = conn.createStatement();
			st.executeUpdate(query);
		} catch(MySQLSyntaxErrorException e) {
			System.out.println("Query trying to execute: " + query);
			e.printStackTrace();
			return false;
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}
		return true;
	}

	/**
	 * Overrides toString()
	 * @return database status
	 */
	public String toString(){
		return "Database";
	}
		
	/**
	 * Checks to see if the username/password combination are correct
	 * @param username Username of user
	 * @param password password corresponding to user
	 * @return the Person object of the user, null otherwise
	 */
	public static Person authenticateUser(String username, String password) {
		// connect to the database
		connect();
		// person object to return
		final Person result = null;
		
		// generate a query that includes all user tables
		String query = "";
		final String delimiter = " UNION ";
		
		// generate a query over all peopleTables
		for(String table : PEOPLE_TABLES) {
			query += "SELECT `username` FROM " + table +  // $codepro.audit.disable stringConcatenationInLoop
			" WHERE `username` = '" + username + "'" + 
			" AND `password` = '" + password + "' " + delimiter;
		}
		// delete the last delimiter
		query = query.substring(0, query.length() - delimiter.length());
		
		//TODO: Remove debug line when done
		//System.out.println("Executing authenticate query: "+ query);
		
		// try to execute the query
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			
			while(rs.next()) { // $codepro.audit.disable
				// attempt to load a user
				Patient pat = loadPatient(rs.getString("username"));
				Nurse nur  = loadNurse(rs.getString("username"));
				Doctor doc = loadDoctor(rs.getString("username"));
				SystemAdmin adm = loadSystemAdmin(rs.getString("username"));
				connect();
				if(pat != null) return pat;
				if(nur != null) return nur;
				if(doc != null) return doc;
				if(adm != null) return adm;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			closeConnection();
		}
		return result;
	}

	/**
	 * Query that checks to see if a result exists
	 * @param query Query to execute
	 * @return Returns true if rows are available, false otherwise
	 */
	public static boolean checkExistence(String query) {
		connect();
		// TODO: Remove debug lines when done
		//System.out.println("Existence query trying to execute: "+query);
		
		boolean result = false;
		try {
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			if (rs.next()) { // $codepro.audit.disable
				result = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeConnection();
		}
		
		return result;
	}
	
	/**
	 * Searches for patients with first names or last names matching
	 * the supplied name
	 * @param fName first name
	 * @param lName last name 
	 * @return a list of patients
	 */
	public static List<Patient> searchPatientByName(String fName, String lName) {
		// generate a query that searches the Patient table
		final String query = "SELECT `username` FROM Patient" +
		" WHERE `first_name` = '" + fName + "' AND `last_name` = '" + lName + "'";
			
		return searchPatientByQuery(query);
	}
	
	/**
	 * Searches for patients with first names or last names matching
	 * the supplied name
	 * @param name name to search by
	 * @return a list of patients
	 */
	public static List<Patient> searchPatientByName(String name) {
		// generate a query that searches the Patient table
		final String query = "SELECT `username` FROM Patient" +
		" WHERE `first_name` = '" + name + "' OR `last_name` = '" + name + "'";
		
		return searchPatientByQuery(query);
	}

	/**
	 * Searches for patients whose first names, last names, and usernames
	 * match the input using mySQL LIKE%...%
	 * @param name name to search by
	 * @return list of patient objects
	 */
	public static List<Patient> searchPatient(String name) {
		// generate a query that searches the Patient table
		final String query = "SELECT `username` FROM Patient" +
		" WHERE `first_name` LIKE '%" + name + "%' " +
				"OR `last_name` LIKE '%" + name + "%' " +
				"OR `username` LIKE '%" + name + "%'";
		
		return searchPatientByQuery(query);
	}

	/**
	 * Returns a list of patients based on the SQL query supplied
	 * @param query Search query to execute
	 * @return a list of Patient Objects
	 */
	public static List<Patient> searchPatientByQuery(String query) {
		connect();
		final List<Patient> result= new ArrayList<Patient>();
		
		//TODO: Remove debug line when done
		//System.out.println("Executing serach query: " + query);
		
		// try to execute the query
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			
			while(rs.next()) { // $codepro.audit.disable
				// get the patient from the database based on their username
				Patient pat = loadPatient(rs.getString("username"));
				result.add(pat);
				// reconnect
				connect();
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			closeConnection();
		}
		return result;
	}
	
	/**
	 * Load an appointment from the database 
	 * @param username username
	 * @return the appointment from the database
	 */
	public static List<Appointment> loadAppointment(String username) {
		connect();
		
		final List<Appointment> result = new ArrayList<Appointment>();
		
		final String query = "SELECT * FROM appointment " +
				"WHERE patient ='" + username + "' " +
				"OR nurse = '" + username + "' " +
				"OR doctor = '" + username + "' " +
				"ORDER BY time";
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			while (rs.next()){
				// get the info from the database
				int id = rs.getInt("id"); // $codepro.audit.disable variableUsage
				String patient = rs.getString("patient");
				String nurse = rs.getString("nurse");
				String doctor = rs.getString("doctor");
				GregorianCalendar start_time = (GregorianCalendar) 
				(Serializer.fromString(rs.getString("start_time")));
				GregorianCalendar end_time = (GregorianCalendar) 
				(Serializer.fromString(rs.getString("end_time")));
				String description = rs.getString("description");
				
				final Appointment app = new Appointment();
				// load it into the object
				app.setId(id);
				if(doctor != null) {
					app.setContact(loadDoctor(doctor));
				}
				else if(nurse != null) {
					app.setContact(loadNurse(nurse));
				}
				app.setPatient(loadPatient(patient));
				app.setMyStartTime(start_time);
				app.setMyEndTime(end_time);
				app.setDescription(description);
				// add it to the list
				result.add(app);
				
				// reconnect to the database
				connect();
			}
		}
		catch (SQLException ex){
			System.err.println(ex.getMessage());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		// return it
		return result;
	}

	/**
	 * Finds all users with the same first and last names
	 * @param firstName the user's first name
	 * @param lastName the users's last name
	 * @return a list of all users with the specified first and last names
	 */
	public static List<Person> searchUsersByName(String firstName, String lastName) {
		// generate a query that includes all user tables
		String query = "";
		final String delimiter = " UNION ";
		
		for(String str : PEOPLE_TABLES) {
			query += "SELECT `username` FROM `" + str +  // $codepro.audit.disable stringConcatenationInLoop
			"` WHERE `first_name` = '" + firstName + "' " +
			"AND `last_name` = '" + lastName + "' " + delimiter;
		}
		// delete the last delimiter
		query = query.substring(0, query.length() - delimiter.length());
		return searchUsersByQuery(query);
	}

	/**
	 * Executes a search query on all users
	 * @param query query to execute
	 * @return Person list of result
	 */
	public static List<Person> searchUsersByQuery(String query) {
		connect();
		final List<Person> result= new ArrayList<Person>();

		//TODO: Remove debug line when done
		//System.out.println("Executing search query: " + query);
		
		// try to execute the query
		try{
			final Statement st = conn.createStatement();
			final ResultSet rs = st.executeQuery(query);
			
			while(rs.next()) { // $codepro.audit.disable
				// get the patient from the database based on their username
				Person pat = loadPatient(rs.getString("username"));
				Person nur = loadNurse(rs.getString("username"));
				Person doc = loadDoctor(rs.getString("username"));
				Person adm = loadSystemAdmin(rs.getString("username"));
				if(pat != null) {
					result.add(pat);
				}
				else if(nur != null) {
					result.add(nur);
				}
				else if(doc != null) {
					result.add(doc);
				}
				else if(adm != null) {
					result.add(adm);
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			System.err.println(ex.getMessage());
		} finally {
			closeConnection();
		}
		return result;
	}

	/**
	 * Generic load user function
	 * Only call this when there are no better options
	 * @param username username 
	 * @return the person with that username
	 */
	public static Person loadUser(String username) {
		Person result = null;
		// get the patient from the database based on their username
		final Person pat = loadPatient(username);
		final Person nur = loadNurse(username);
		final Person doc = loadDoctor(username);
		final Person adm = loadSystemAdmin(username);
		if(pat != null) {
			result = pat;
		}
		else if(nur != null) {
			result = nur;
		}
		else if(doc != null) {
			result = doc;
		}
		else if(adm != null) {
			result = adm;
		}
		return result;
	}

	/**
	 * Deletes the user from the database
	 * @param username username of the user
	 * @return true if successful, false otherwise
	 */
	public static boolean deleteUser(String username) {
		// load the user
		final Person pat = loadUser(username);
		if(pat == null) return false;
		// delete the user if not null
		return pat.delete();
	}
}
